package cn.ainit.fx;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import cn.ainit.sys.query.datastore.DataStore;
import cn.ainit.sys.query.entity.QueryJson;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.ainit.sys.common.dao.BusinessBaseDao;
import cn.ainit.sys.utils.DateTimeStr;
import cn.ainit.sys.utils.UUID32;

@Transactional
@Repository
public class FaxingDaoImpl extends BusinessBaseDao implements FaxingDao {

    /**
     * 获取征订单折扣
     */
    //(vi_hId   vi_dsId   vi_clientId
    @Override
    public Map<String, Object> getDiscount(List<String> keyVal, HttpServletRequest request, HttpSession session) throws Exception {
        String sql = " select PK_FX.uf_getXSDiscountByHid('" + keyVal.get(0) + "','" + keyVal.get(1) + "','" + keyVal.get(2) + "') H_DISCOUNT from dual";
        return this.getJdbcTemplate().queryForMap(sql);
    }

    @Override
    public List execSql(String sql) throws Exception {
        List list = new ArrayList();
        try {
            list = this.getJdbcTemplate().queryForList(sql);
            if (list != null) {
                return list;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Double execFuncDZ(String clientId) throws Exception {
        Double dzjy = 0.0;
        Connection conn = null;
        CallableStatement cs = null;
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{?=call PK_FX_DZ.uf_getClientDz_LastUnSkMoney(?,?,?)}";
            cs = conn.prepareCall(sql);
            cs.registerOutParameter(1, Types.DOUBLE);
            cs.setString(2, clientId);
            cs.setString(3, "");
            cs.setDate(4, new Date(dbType));
            cs.execute();
            dzjy = cs.getDouble(1);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return dzjy;
    }

    @Override
    public Double execYSFunc(String clientId) throws Exception {
        Double ysjy = 0.0;
        Connection conn = null;
        CallableStatement cs = null;
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{?=call PK_FX_DZ.UF_GETYSJY(?)}";
            cs = conn.prepareCall(sql);
            cs.registerOutParameter(1, Types.DOUBLE);
            cs.setString(2, clientId);
            cs.execute();
            ysjy = cs.getDouble(1);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return ysjy;
    }

    @Override
    public Double execSKCYFunc(String clientId) throws Exception {
        Double skcy = 0.0;
        Connection conn = null;
        CallableStatement cs = null;
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{?=call PK_FX_DZ.UF_GETCHARGECHAYI(?)}";
            cs = conn.prepareCall(sql);
            cs.registerOutParameter(1, Types.DOUBLE);
            cs.setString(2, clientId);
            cs.execute();
            skcy = cs.getDouble(1);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return skcy;
    }

    @Override
    public Double execZQKFunc(String clientId) throws Exception {
        Double zqk = 0.0;
        Connection conn = null;
        CallableStatement cs = null;
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{?=call PK_FX_DZ.UF_GETZQKFUNC(?)}";
            cs = conn.prepareCall(sql);
            cs.registerOutParameter(1, Types.DOUBLE);
            cs.setString(2, clientId);
            cs.execute();
            zqk = cs.getDouble(1);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return zqk;
    }

    @Override
    public Map<String, Object> getStockNumByHidStock(List<String> keyVal,
                                                     HttpServletRequest request, HttpSession session) throws Exception {
        String sql = " select nvl(PK_FX.uf_getStockNumByHidStock('" + keyVal.get(0) + "','" + keyVal.get(1) + "'),0) STOCK_NUM from dual";
        return this.getJdbcTemplate().queryForMap(sql);
    }

    /**
     * 添加采样数据，如果数据已经存在则返回待用户确认
     */
    @Override
    public SamplingList addSamples(SamplingList samples) throws Exception {
        SamplingList reSamplings = new SamplingList();
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            //先将重复的商品 累加数量
            Map<String, Integer> tmp = new HashMap<String, Integer>();
            for (int i = 0; i < samples.getSamplings().size(); i++) {
                if (tmp.containsKey(samples.getSamplings().get(i).getHid() + "" + samples.getSamplings().get(i).getDiscount())) {
                    Integer k = tmp.get(samples.getSamplings().get(i).getHid() + "" + samples.getSamplings().get(i).getDiscount());
                    samples.getSamplings().get(k).setNum(samples.getSamplings().get(k).getNum() + samples.getSamplings().get(i).getNum());
                    samples.getSamplings().remove(i);
                    i--;
                } else {
                    tmp.put(samples.getSamplings().get(i).getHid() + "" + samples.getSamplings().get(i).getDiscount(), i);
                }
            }


            //根据O_ID  H_ID  DISCOUNT 查询数据库中存在的商品 存在缓存Map中
            StringBuffer hids = new StringBuffer("(1=1)");
            for (int i = 0; i < samples.getSamplings().size(); i++) {
                if (i == 0) {
                    hids.append(" AND (");
                } else {
                    hids.append(" OR ");
                }
                hids.append(" (H_ID='");
                hids.append(samples.getSamplings().get(i).getHid() + "' AND DISCOUNT=");
                hids.append(samples.getSamplings().get(i).getDiscount() == null ? "0" : samples.getSamplings().get(i).getDiscount().toString() + ")");
                if (i == (samples.getSamplings().size() - 1)) {
                    hids.append(")");
                }
            }
            String sql = " select H_ID,H_NUM,DISCOUNT FROM SYS_FX_SAMPLING WHERE O_ID = '" + samples.getOid() + "' AND (" + hids + ")";
            //前台传入在数据库中已存在的数据
            List<Map<String, Object>> exitHids = this.getJdbcTemplate().queryForList(sql);
            List<Sampling> exitSampls = new ArrayList<Sampling>();
            for (int i = 0; i < exitHids.size(); i++) {
                Sampling sam = new Sampling();
                sam.setHid(exitHids.get(i).get("H_ID").toString());
                sam.setDiscount(Double.parseDouble(exitHids.get(i).get("DISCOUNT").toString()));
                sam.setOldNum(Double.parseDouble(exitHids.get(i).get("H_NUM").toString()));
                exitSampls.add(sam);
            }
//			SYS_FX_SAMPLING
//			(
//			  S_ID VARCHAR2(32),
//			  O_ID       VARCHAR2(32 CHAR), 
//			  H_ID          VARCHAR2(32 CHAR),
//			  STOCK_ID       VARCHAR2(32 CHAR),
//			  DISCOUNT     NUMBER(12,4),
//			  H_NUM         NUMBER(12,4),
//			  H_PRICE      NUMBER(12,4),
//			  H_STATES      VARCHAR2(32 CHAR),
//			  DS_ID         VARCHAR2(32),
//			  MEMO    
            reSamplings.setDsid(samples.getDsid());
            reSamplings.setFlag("00");
            reSamplings.setInfo("");
            reSamplings.setOid(samples.getOid());
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            sql = " insert into SYS_FX_SAMPLING values (?,?,?,?,?,?,?,?,?,?) ";
            String exitSql = "";
            for (int i = 0; i < samples.getSamplings().size(); i++) {
                exitSql = " select count(1) from SYS_FX_SAMPLING where O_ID = '" + samples.getOid() + "' AND DISCOUNT = " + samples.getSamplings().get(i).getDiscount() + " AND H_ID = '" + samples.getSamplings().get(i).getHid() + "'";
                //如果数据库中存在这个商品的，就将这个商品返回确认数据
                if (this.getJdbcTemplate().queryForInt(exitSql) > 0) {
                    for (int j = 0; j < exitSampls.size(); j++) {
                        if (samples.getSamplings().get(i).getHid().equals(exitSampls.get(j).getHid())
                                && samples.getSamplings().get(i).getDiscount().equals(exitSampls.get(j).getDiscount())) {
                            exitSampls.get(j).setNum(samples.getSamplings().get(i).getNum());
                        }
                    }
                    continue;
                }
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, UUID32.newGuid());
                stmt.setString(2, samples.getOid());
                stmt.setString(3, samples.getSamplings().get(i).getHid());
                stmt.setString(4, samples.getSamplings().get(i).getStockid() == null ? "" : samples.getSamplings().get(i).getStockid());
                stmt.setDouble(5, samples.getSamplings().get(i).getDiscount() == null ? 0.0 : samples.getSamplings().get(i).getDiscount());
                stmt.setDouble(6, samples.getSamplings().get(i).getNum() == null ? 0.0 : Math.abs(samples.getSamplings().get(i).getNum()));
                stmt.setDouble(7, samples.getSamplings().get(i).getPrice() == null ? 0.0 : samples.getSamplings().get(i).getPrice());
                stmt.setString(8, samples.getSamplings().get(i).getStates());
                stmt.setString(9, samples.getDsid());
                stmt.setString(10, "");
                stmt.execute();
                stmt.close();
            }
            conn.commit();
            reSamplings.setSamplings(exitSampls);
            reSamplings.setMsg("success");
        } catch (Exception e) {
            e.printStackTrace();
            reSamplings.setMsg("fail");
            reSamplings.setInfo(e.getMessage());
            return reSamplings;
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return reSamplings;
    }

    /**
     * 更新采样数量
     */
    @Override
    public boolean addSamplesCount(SamplingList samples) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            String sql = " update SYS_FX_SAMPLING S SET S.H_NUM = S.H_NUM + ? WHERE S.O_ID = '" + samples.getOid() + "' AND DISCOUNT =? AND H_ID = ?";
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            for (int i = 0; i < samples.getSamplings().size(); i++) {
                stmt = conn.prepareStatement(sql);
                stmt.setDouble(1, samples.getSamplings().get(i).getNum() == null ? 0.0 : samples.getSamplings().get(i).getNum());
                stmt.setDouble(2, samples.getSamplings().get(i).getDiscount() == null ? 0.0 : samples.getSamplings().get(i).getDiscount());
                stmt.setString(3, samples.getSamplings().get(i).getHid());
                stmt.execute();
                stmt.close();
            }
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return true;
    }


    /**
     * 覆盖采样数据
     */
    @Override
    public boolean saveSamples(SamplingList samples) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            //先将重复的商品 累加数量
            Map<String, Integer> tmp = new HashMap<String, Integer>();
            for (int i = 0; i < samples.getSamplings().size(); i++) {
                if (tmp.containsKey(samples.getSamplings().get(i).getHid() + "" + samples.getSamplings().get(i).getDiscount())) {
                    Integer k = tmp.get(samples.getSamplings().get(i).getHid() + "" + samples.getSamplings().get(i).getDiscount());
                    samples.getSamplings().get(k).setNum(samples.getSamplings().get(k).getNum() + samples.getSamplings().get(i).getNum());
                    samples.getSamplings().remove(i);
                    i--;
                } else {
                    tmp.put(samples.getSamplings().get(i).getHid() + "" + samples.getSamplings().get(i).getDiscount(), i);
                }
            }
            //判断如果存在同一商品的数量和折扣相同， 则直接更新
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String delSql = "delete from SYS_FX_SAMPLING s where s.O_ID = '" + samples.getOid() + "'";
            stmt = conn.prepareStatement(delSql);
            stmt.execute();
            stmt.close();
            String sqlIns = " insert into SYS_FX_SAMPLING values (?,?,?,?,?,?,?,?,?,?) ";
            String sqlUp = " update SYS_FX_SAMPLING s set H_ID = ?,STOCK_ID=?,DISCOUNT=?,H_NUM=?,H_PRICE=? where s.O_ID = '" + samples.getOid() + "' and H_ID =?,DISCOUNT=? ";
            String sql = "";
            Map<String, String> existMap = new HashMap<String, String>();


            for (int i = 0; i < samples.getSamplings().size(); i++) {
                if (existMap.containsKey(samples.getSamplings().get(i).getHid() + samples.getSamplings().get(i).getDiscount() + "" + samples.getSamplings().get(i).getNum())) {
                    sql = sqlUp;//更新数量
                    stmt = conn.prepareStatement(sql);
                    stmt.setString(1, samples.getSamplings().get(i).getHid());
                    stmt.setString(2, samples.getSamplings().get(i).getStockid() == null ? "" : samples.getSamplings().get(i).getStockid());
                    stmt.setDouble(3, samples.getSamplings().get(i).getDiscount() == null ? 0.0 : samples.getSamplings().get(i).getDiscount());
                    stmt.setDouble(4, samples.getSamplings().get(i).getNum() == null ? 0.0 : Math.abs(samples.getSamplings().get(i).getNum()));
                    stmt.setDouble(5, samples.getSamplings().get(i).getPrice() == null ? 0.0 : samples.getSamplings().get(i).getPrice());
                    stmt.setString(6, samples.getSamplings().get(i).getHid());
                    stmt.setDouble(7, samples.getSamplings().get(i).getDiscount() == null ? 0.0 : samples.getSamplings().get(i).getDiscount());
                } else {
                    sql = sqlIns;//添加新品
                    existMap.put(samples.getSamplings().get(i).getHid() + samples.getSamplings().get(i).getDiscount() + "" + samples.getSamplings().get(i).getNum(), samples.getSamplings().get(i).getHid());
                    stmt = conn.prepareStatement(sql);
                    stmt.setString(1, UUID32.newGuid());
                    stmt.setString(2, samples.getOid());
                    stmt.setString(3, samples.getSamplings().get(i).getHid());
                    stmt.setString(4, samples.getSamplings().get(i).getStockid() == null ? "" : samples.getSamplings().get(i).getStockid());
                    stmt.setDouble(5, samples.getSamplings().get(i).getDiscount() == null ? 0.0 : samples.getSamplings().get(i).getDiscount());
                    stmt.setDouble(6, samples.getSamplings().get(i).getNum() == null ? 0.0 : Math.abs(samples.getSamplings().get(i).getNum()));
                    stmt.setDouble(7, samples.getSamplings().get(i).getPrice() == null ? 0.0 : samples.getSamplings().get(i).getPrice());
                    stmt.setString(8, samples.getSamplings().get(i).getStates());
                    stmt.setString(9, samples.getDsid());
                    stmt.setString(10, "");
                }
                stmt.execute();
                stmt.close();
            }
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return true;
    }

    /**
     * 提取采样
     */
    @Override
    public SamplingList getSamples(String oid) throws Exception {
//		SYS_FX_SAMPLING
//		(
//		  S_ID VARCHAR2(32),
//		  O_ID       VARCHAR2(32 CHAR), 
//		  H_ID          VARCHAR2(32 CHAR),
//		  STOCK_ID       VARCHAR2(32 CHAR),
//		  DISCOUNT     NUMBER(12,4),
//		  H_NUM         NUMBER(12,4),
//		  H_PRICE      NUMBER(12,4),
//		  H_STATES      VARCHAR2(32 CHAR),
//		  DS_ID         VARCHAR2(32),
//		  MEMO    
        String sql = "  select SFS.H_ID,SFS.STOCK_ID,SFS.DISCOUNT,SFS.H_NUM,SFS.H_PRICE, " +
                "  SP.H_NAME,SP.H_ISBN,SP.H_PRICE,SP.H_TYPE FROM SYS_FX_SAMPLING SFS LEFT JOIN BS_PRODUCT SP " +
                "  ON SFS.H_ID = SP.H_ID  " +
                "  WHERE O_ID = '" + oid + "'";
        List<Map<String, Object>> listMap = this.getJdbcTemplate().queryForList(sql);
        SamplingList samplingList = new SamplingList();
        samplingList.setSamplingsMap(listMap);
        samplingList.setMsg("success");
        return samplingList;
    }

    @Override
    public boolean saveDZYSFunc(String skId, String cid, List<Map<String, String>> ysData, String manId)
            throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "insert into fx_sk_ys(dz_ys_id,sk_id,ys_id,ys_money_occupy,input_man,input_date,CLIENT_ID)" +
                    "values(?,?,?,?,'" + manId + "',?,?)";
            String sqlUpd = "UPDATE FX_SK_YS SET YS_MONEY_OCCUPY = ? WHERE SK_ID = ? AND YS_ID = ?";
            int countNum = 0;
            String selSql = "";
            if (!"".equals(skId) && skId != null) {
                for (int i = 0; i < ysData.size(); i++) {
                    selSql = "SELECT COUNT(1) FROM FX_SK_YS D WHERE D.SK_ID = '" + skId + "' AND D.YS_ID = '" + ysData.get(i).get("ysId") + "'";
                    countNum = this.getJdbcTemplate().queryForInt(selSql);
                    if (countNum > 0) {
                        stmt = conn.prepareStatement(sqlUpd);
                        stmt.setString(1, ysData.get(i).get("useMoney"));
                        stmt.setString(2, skId);
                        stmt.setString(3, ysData.get(i).get("ysId"));
                        stmt.execute();
                        stmt.close();
                    } else {
                        stmt = conn.prepareStatement(sql);
                        stmt.setString(1, UUID32.newGuid());
                        stmt.setString(2, skId);
                        stmt.setString(3, ysData.get(i).get("ysId"));
                        stmt.setString(4, ysData.get(i).get("useMoney"));
                        stmt.setDate(5, java.sql.Date.valueOf(DateTimeStr.getDateTime()));
                        stmt.setString(6, cid);
                        stmt.execute();
                        stmt.execute("commit");
                        conn.commit();
                        stmt.close();
                    }

//					//修改预收表
//					stmt = conn.prepareStatement(updSql);
//					stmt.setFloat(1,Float.parseFloat(ysData.get(i).get("useMoney")));
//					stmt.setString(2, ysData.get(i).get("ysId"));
//					stmt.execute();
//					stmt.close();
                }
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return false;
    }

    @Override
    public boolean saveXSXTFunc(String xtId, List<Map<String, String>> xtData,
                                String manId, String clientId) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "INSERT INTO FX_XT_CRUSH_XS C(CRU_ID,CLIENT_ID,XS_ID,XS_ITEM_ID,XT_ID,AMOUNT_OCCUPY,INPUT_DATE)" +
                    "VALUES('" + UUID32.newGuid() + "','" + clientId + "',?,?,?,?,?) ";
            String sqlUpd = "UPDATE FX_XT_CRUSH_XS C SET C.AMOUNT_OCCUPY = ? WHERE C.XS_ID =? AND C.XT_ID = ? AND C.XS_ITEM_ID = ?";
            int countNum = 0;
            String selSql = "";
            if (!"".equals(xtId) && xtId != null) {
                for (int i = 0; i < xtData.size(); i++) {
                    selSql = "SELECT COUNT(1) FROM FX_XT_CRUSH_XS C WHERE C.XT_ID = '" + xtId + "' AND C.XS_ITEM_ID = '" + xtData.get(i).get("xsItemId") + "'";
                    countNum = this.getJdbcTemplate().queryForInt(selSql);
                    if (countNum > 0) {
                        stmt = conn.prepareStatement(sqlUpd);
                        stmt.setString(1, xtData.get(i).get("useAmount"));
                        stmt.setString(2, xtData.get(i).get("xsId"));
                        stmt.setString(3, xtId);
                        stmt.setString(4, xtData.get(i).get("xsItemId"));
                        stmt.execute();
                        stmt.close();
                    } else {
                        stmt = conn.prepareStatement(sql);
                        stmt.setString(1, xtData.get(i).get("xsId"));
                        stmt.setString(2, xtData.get(i).get("xsItemId"));
                        stmt.setString(3, xtId);
                        stmt.setString(4, xtData.get(i).get("useAmount"));
                        stmt.setDate(5, java.sql.Date.valueOf(DateTimeStr.getDateTime()));
                        stmt.execute();
                        stmt.execute("commit");
                        conn.commit();
                        stmt.close();
                    }
                }
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return false;
    }

    @Override
    public boolean saveTempDataFunc(List<Map<String, String>> selRows)
            throws Exception {
        boolean flag = false;
        List<Object[]> list = null;
        try {
            String sql = "DELETE FROM fx_xtcj";
            flag = this.executeSQL(sql);
            if (flag) {
                String sql1 = "INSERT INTO fx_xtcj(h_amount_sy,t_id,xs_id,xs_item_id,h_id,h_discount,h_discount_mod,client_id,xs_amount) VALUES(?,sys_guid(),?,?,?,?,?,?,?)";
                list = new ArrayList<Object[]>();
                for (int i = 0; i < selRows.size(); i++) {
                    String value = selRows.get(i).get("H_AMOUNT_SY");
                    if (value != null && !"".equals(value.trim()) && Integer.parseInt(value) > 0) {
                        value = "-" + value;
                    } else {
                        value = "0";
                    }
                    list.add(new Object[]{Integer.parseInt(value), selRows.get(i).get("PX_ID"), selRows.get(i).get("PX_ITEM_ID"), selRows.get(i).get("H_ID"), selRows.get(i).get("H_DISCOUNT"), selRows.get(i).get("H_DISCOUNT_MOD"), selRows.get(i).get("CLIENT_ID"), selRows.get(i).get("H_AMOUNT")});
                }
                int[] lengthF = this.jdbcTemplate.batchUpdate(sql1, list);
                if (lengthF.length == selRows.size()) {
                    flag = true;
                } else {
                    flag = false;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }

    @Override
    public boolean saveCacheYS(List<Map<String, String>> selRows, String skid) throws Exception {
        boolean flag = false;
        List<Object[]> list = null;
        Date date = new Date(0);
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");//可以方便地修改日期格式
        String SysDate = dateFormat.format(date);
        try {
            String sql = "DELETE FROM fx_sk_ys_cache";
            flag = this.executeSQL(sql);
            if (flag) {
                String sql1 = "INSERT INTO fx_sk_ys_cache(DZ_YS_ID,YS_ID,SK_ID,YS_MONEY_USE,INPUT_MAN,INPUT_DATE,YS_MONEY_OCCUPY,USE_MONEY_TX) VALUES(sys_guid(),?,?,?,?,?,?,?)";
                list = new ArrayList<Object[]>();
                for (int i = 0; i < selRows.size(); i++) {
                    list.add(new Object[]{selRows.get(i).get("YS_ID"), skid, selRows.get(i).get("YS_MONEY_USED"), selRows.get(i).get(UUID32.newGuid()), selRows.get(i).get(SysDate), selRows.get(i).get("USE_MONEY_TX"), selRows.get(i).get("USE_MONEY_TX")});
                }
                int[] lengthF = this.jdbcTemplate.batchUpdate(sql1, list);
                if (lengthF.length == selRows.size()) {
                    flag = true;
                } else {
                    flag = false;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }

    @Override
    public boolean saveCacheDK(List<Map<String, String>> selRows, String skid) throws Exception {
        String uuid = UUID32.newGuid();
        boolean flag = false;
        List<Object[]> list = null;
        Date date = new Date(0);
        try {
            String sql = "DELETE FROM fx_sk_dk_cache";
            flag = this.executeSQL(sql);
            if (flag) {
                String sql1 = "INSERT INTO fx_sk_dk_cache(SK_DK_ID,DK_ID,SK_ID,DK_MONEY_USE,INPUT_MAN,INPUT_DATE,DK_MONEY_OCCUPY) VALUES(sys_guid(),?,?,?,?,?,?)";
                list = new ArrayList<Object[]>();
                for (int i = 0; i < selRows.size(); i++) {
                    list.add(new Object[]{selRows.get(i).get("DK_ID"), skid, selRows.get(i).get("USE_MONEY_TX"), uuid, date, selRows.get(i).get("USE_MONEY_TX")});
                }
                int[] lengthF = this.jdbcTemplate.batchUpdate(sql1, list);
                if (lengthF.length == selRows.size()) {
                    flag = true;
                } else {
                    flag = false;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }

    @Override
    public boolean delTemoDataFunc(List<Map<String, String>> delRows)
            throws Exception {
        boolean flag = false;
        Connection conn = null;
        CallableStatement cs = null;
        Double discount = 0.0;
        Double discountMod = 0.0;
        String rutStr = "";
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{Call PK_FX_PX.up_fxpt_cjDel(?,?,?,?,?)}";
            for (int i = 0; i < delRows.size(); i++) {
                if (delRows.get(i).get("H_DISCOUNT").equals("") || delRows.get(i).get("H_DISCOUNT").equals("null")) {
                    discount = 0.0;
                } else {
                    discount = Double.parseDouble(delRows.get(i).get("H_DISCOUNT"));
                }
                if (delRows.get(i).get("H_DISCOUNT_MOD").equals("") || delRows.get(i).get("H_DISCOUNT_MOD").equals("null")) {
                    discountMod = 0.0;
                } else {
                    discountMod = Double.parseDouble(delRows.get(i).get("H_DISCOUNT_MOD"));
                }
                cs = conn.prepareCall(sql);
                cs.setString(1, delRows.get(i).get("H_ID"));
                cs.setDouble(2, discount);
                cs.setDouble(3, discountMod);
                cs.setString(4, delRows.get(i).get("H_AMOUNT").toString());
                cs.registerOutParameter(5, oracle.jdbc.OracleTypes.VARCHAR);
                cs.execute();
                rutStr = cs.getString(5);
                conn.commit();
                cs.close();
            }
            if (rutStr == "success") {
                flag = true;
            } else {
                flag = false;
            }
        } catch (Exception e) {
            flag = false;
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return flag;
    }

    @Override
    public boolean execSqlB(String sql) throws Exception {
        return this.executeSQL(sql);
    }

    @Override
    public boolean saveSKDKFunc(String skId, String cid, List<Map<String, String>> dkData,
                                String manId) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "INSERT INTO FX_SK_DK(SK_DK_ID,Sk_Id,Dk_Id,DK_MONEY_OCCUPY,INPUT_MAN,INPUT_DATE,CLIENT_ID) " +
                    "VALUES(?,?,?,?,'" + manId + "',?,?)";
            String sqlUpd = "UPDATE FX_SK_DK SET DK_MONEY_OCCUPY = ? WHERE SK_ID = ? AND DK_ID = ?";
            int countNum = 0;
            String selSql = "";
            if (!"".equals(skId) && skId != null) {
                for (int i = 0; i < dkData.size(); i++) {
                    selSql = "SELECT COUNT(1) FROM FX_SK_DK D WHERE D.SK_ID = '" + skId + "' AND DK_ID = '" + dkData.get(i).get("dkId") + "'";
                    countNum = this.getJdbcTemplate().queryForInt(selSql);
                    if (countNum > 0) {
                        stmt = conn.prepareStatement(sqlUpd);
                        stmt.setString(1, dkData.get(i).get("useMoney"));
                        stmt.setString(2, skId);
                        stmt.setString(3, dkData.get(i).get("dkId"));
                        stmt.execute();
                        stmt.close();
                    } else {
                        stmt = conn.prepareStatement(sql);
                        stmt.setString(1, UUID32.newGuid());
                        stmt.setString(2, skId);
                        stmt.setString(3, dkData.get(i).get("dkId"));
                        stmt.setString(4, dkData.get(i).get("useMoney"));
                        stmt.setDate(5, java.sql.Date.valueOf(DateTimeStr.getDateTime()));
                        stmt.setString(6, cid);
                        stmt.execute();
                        stmt.execute("commit");
                        conn.commit();
                        stmt.close();
                    }

//					//修改预收表
//					stmt = conn.prepareStatement(updSql);
//					stmt.setFloat(1,Float.parseFloat(ysData.get(i).get("useMoney")));
//					stmt.setString(2, ysData.get(i).get("ysId"));
//					stmt.execute();
//					stmt.close();
                }
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return false;
    }

    @Override
    public List getDzysByJsId(String skId) throws Exception {
        List list = new ArrayList();
        try {
            String sql = "SELECT Z.SK_ID,Z.YS_ID,Z.YS_MONEY_OCCUPY,Z.USE_MONEY_TX FROM FX_SK_YS Z WHERE Z.SK_ID = '" + skId + "'";
            list = this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List getDkskBySkId(String skId) throws Exception {
        List list = new ArrayList();
        try {
            String sql = "SELECT S.SK_ID,S.DK_ID,S.DK_MONEY_OCCUPY FROM FX_SK_DK S WHERE S.SK_ID = '" + skId + "'";
            list = this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List getCJxtByXtId(String xtId) throws Exception {
        List list = new ArrayList();
        try {
            String sql = "SELECT CX.XS_ID,CX.XS_ITEM_ID,CX.XT_ID,CX.AMOUNT_OCCUPY FROM FX_XT_CRUSH_XS CX WHERE CX.XT_ID = '" + xtId + "'";
            list = this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List getTempData() throws Exception {
        List list = new ArrayList();
        try {
            String sql = "SELECT * FROM fx_xtcj";
            list = this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List findYsData(String skId, String clientId) throws Exception {
        List list = new ArrayList();
        try {
            String sql = "SELECT * FROM fx_sk_ys s where s.SK_ID = '" + skId + "' and s.CLIENT_ID = '" + clientId + "'";
            list = this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    //纸厂核销的客户预收数据
    @Override
    public List findZcYsData(String VENDORID) throws Exception {
        List list = new ArrayList();
        try {
            String sql = "SELECT * FROM zc_advance s where s.VENDOR_ID = '" + VENDORID + "' and s.IS_VERIFY='1'";
            list = this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List findDkData(String skId, String clientId) throws Exception {
        List list = new ArrayList();
        try {
            String sql = "SELECT * FROM fx_sk_dk s where s.SK_ID = '" + skId + "' and s.CLIENT_ID = '" + clientId + "'";
            list = this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List getDzItemByJSId(String djId, String flag) throws Exception {
        List list = new ArrayList();
        String sql = "";
        try {
            if ("k".equals(flag)) {  //开票单  要查询结算单
                sql = "SELECT J.INPUT_DATE,I.* FROM FX_CLIENTS_JSLIST_ITEM I LEFT JOIN FX_CLIENTS_JSLIST J ON I.JS_ID = J.JS_ID WHERE I.JS_ID = '" + djId + "'";
            } else if ("s".equals(flag)) { //收款单  要查询开票单
                sql = " SELECT K.INVOICE_DATE,KI.* FROM FX_INVOICE_KP_ITEM KI LEFT JOIN FX_INVOICE_KP K ON KI.INV_ID = K.INV_ID  WHERE KI.INV_ID = '" + djId + "' ";
            }
            list = this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    //根据客户Id查询客户地址的数量
    @Override
    public Integer findCountByCId(String cid) throws Exception {
        int count = 0;
        try {
            String asql = "SELECT s.c_address_send FROM bs_clients s WHERE s.clients_id = '" + cid + "'";
            String sinId = this.jdbcTemplate.queryForObject(asql, String.class);
            String bsql = "SELECT COUNT(*) FROM MULTI_RECIEVER_ADDRESS S WHERE S.SIN_ID = '" + sinId + "' AND S.IS_SHOW = '1'";
            count = this.jdbcTemplate.queryForInt(bsql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return count;
    }

    @Override
    public String findShelfId(String stockId, String hId) throws Exception {
        String shelfId = "";
        Integer count = 0;
        try {
            String sqlCount = "SELECT COUNT(1) FROM FX_SHELVES_AMOUNT A WHERE A.STOCK_ID = '" + stockId + "' AND A.H_ID = '" + hId + "' AND NVL(A.H_AMOUNT,'0') != 0";
            count = this.jdbcTemplate.queryForInt(sqlCount);
            String sql = "SELECT A.SHELF_ID FROM FX_SHELVES_AMOUNT A WHERE A.STOCK_ID = '" + stockId + "' AND A.H_ID = '" + hId + "' AND NVL(A.H_AMOUNT,'0') != 0";
            if (count > 0) {
                shelfId = this.jdbcTemplate.queryForObject(sql, String.class);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return shelfId;
    }

    @Override
    public String getAmountByHid(String hId) throws Exception {
        String hAmount = "";
        try {
            String sql = "SELECT s.h_amount FROM FX_SHELVES_AMOUNT s WHERE s.h_id = '" + hId + "'";
            hAmount = this.jdbcTemplate.queryForObject(sql, String.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return hAmount;
    }

    @Override
    public String findAdress(String clientId) throws Exception {
        String adressId = "";
        try {
            String asql = "SELECT s.c_address_send FROM bs_clients s WHERE s.clients_id = '" + clientId + "'";
            String sinId = this.jdbcTemplate.queryForObject(asql, String.class);
            String bsql = "SELECT * FROM MULTI_RECIEVER_ADDRESS S WHERE S.SIN_ID = '" + sinId + "' AND S.IS_SHOW = '1'";
            List<Map<String, Object>> list = this.jdbcTemplate.queryForList(bsql);
            if (list != null && list.size() != 0) {
                if (list.size() > 1) {
                    adressId = "";
                } else if (list.size() == 1) {
                    adressId = list.get(0).get("ID").toString();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return adressId;
    }

    @Override
    public String findStockId(String peItemId) throws Exception {
        String stcokId = "";
        Connection conn = null;
        CallableStatement cs = null;
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{?=call PK_TEST.UF_GETSTOCKID(?)}";
            cs = conn.prepareCall(sql);
            cs.registerOutParameter(1, Types.VARCHAR);
            cs.setString(2, peItemId);
            cs.execute();
            stcokId = cs.getString(1);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return stcokId;
    }

    @Override
    public int execSqlToInt(String sql) throws Exception {
        return this.jdbcTemplate.queryForInt(sql);
    }

    @Override
    public String zqByClientId(String clientId) throws Exception {
        String clientZq = "";
        try {
            String asql = "SELECT s.DAY_ZT FROM bs_clients s WHERE s.clients_id = '" + clientId + "'";
            clientZq = this.jdbcTemplate.queryForObject(asql, String.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return clientZq;
    }

    @Override
    public List listByClientId(String clientId) throws Exception {
        List dataList = new ArrayList();
        Connection conn = null;
        CallableStatement cs = null;
        String sqlText = "";
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{Call PK_FX_DZ.UP_GETTC_SELECT(?,?)}";
            cs = conn.prepareCall(sql);
            cs.setString(1, clientId);
            cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
            cs.execute();
            sqlText = cs.getString(2);
            sqlText = "select * from (" + sqlText + ") t where t.clients_id = '" + clientId + "'";
            System.out.println("===============销售对账查询得到的sql语句：" + sqlText);
            dataList = this.jdbcTemplate.queryForList(sqlText);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return dataList;
    }

    @Override
    public List listByoid(String oIdLastgr, String hid) throws Exception {
        List dataList = new ArrayList();
        Connection conn = null;
        CallableStatement cs = null;
        String sqlText = "";
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{Call PK_FX_DZ.UP_GETKH_SELECT(?,?,?)}";
            cs = conn.prepareCall(sql);
            cs.setString(1, oIdLastgr);
            cs.setString(2, hid);
            cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);
            cs.execute();
            sqlText = cs.getString(3);
            System.out.println("根据片区经理和商品名称所拉取的数据：" + sqlText);
            dataList = this.jdbcTemplate.queryForList(sqlText);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return dataList;
    }


    @Override
    public int pdCountByKPId(String kpId) throws Exception {
        int count = 0;
        try {
            String sql = "SELECT COUNT(1) FROM FX_CHARGE C WHERE C.SOURCE_ID = '" + kpId + "'";
            count = this.jdbcTemplate.queryForInt(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return count;
    }

    @Override
    public String wyVerifyYW(List<String> runIds, String manId) throws Exception {
        Connection conn = null;
        CallableStatement cs = null;
        String returnStr = "";
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{Call PK_FX.UP_DFHQ_YWVERIFY(?,?,?)}";
            for (int i = 0; i < runIds.size(); i++) {
                cs = conn.prepareCall(sql);
                cs.setString(1, runIds.get(i));
                cs.setString(2, manId);
                cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);
                cs.execute();
                returnStr = cs.getString(3);
                cs.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return returnStr;
    }

    @Override
    public String wyVerifyDeap(List<String> runIds, String manId) throws Exception {
        Connection conn = null;
        CallableStatement cs = null;
        String returnStr = "";
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{Call PK_FX.UP_DFHQ_BMVERIFY(?,?,?)}";
            for (int i = 0; i < runIds.size(); i++) {
                cs = conn.prepareCall(sql);
                cs.setString(1, runIds.get(i));
                cs.setString(2, manId);
                cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);
                cs.execute();
                returnStr = cs.getString(3);
                cs.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return returnStr;
    }

    @Override
    public String gscalCulaButn(List<String> runIds, String manId) throws Exception {
        Connection conn = null;
        CallableStatement cs = null;
        String returnStr = "";
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{Call PK_YZ_CBHS.UP_FORMCALCUL(?,?)}";
            for (int i = 0; i < runIds.size(); i++) {
                cs = conn.prepareCall(sql);
                cs.setString(1, runIds.get(i));
                cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
                cs.execute();
                returnStr = cs.getString(2);
                cs.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return returnStr;
    }

    @Override
    public String amountUnitPrice(List<String> runIds, String manId) throws Exception {
        Connection conn = null;
        CallableStatement cs = null;
        String returnStr = "";
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{Call PK_YZ_CBHS.UP_AMOUNTUNITPRICE(?,?)}";
            for (int i = 0; i < runIds.size(); i++) {
                cs = conn.prepareCall(sql);
                cs.setString(1, runIds.get(i));
                cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
                cs.execute();
                returnStr = cs.getString(2);
                cs.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return returnStr;
    }

    @Override
    public boolean bmSaveFunc(List<Map<String, String>> allRows)
            throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "UPDATE FX_HAND_OUT H SET H_AMOUNT_BM = ?,H_AMOUNT_YW = ?,H_DISCOUNT = ?,MEMO = ?,C_ZD_ID=? WHERE H.RUN_ID = ?";
            for (int i = 0; i < allRows.size(); i++) {
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, allRows.get(i).get("H_AMOUNT_BM"));
                stmt.setString(2, allRows.get(i).get("H_AMOUNT_YW"));
                stmt.setString(3, allRows.get(i).get("H_DISCOUNT"));
                stmt.setString(4, allRows.get(i).get("MEMO"));
                stmt.setString(5, allRows.get(i).get("C_ZD_ID"));
                stmt.setString(6, allRows.get(i).get("RUN_ID"));

                stmt.execute();
                conn.commit();
                stmt.close();
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return false;
    }


    @Override
    public String sureFH(String manId, String runids) throws Exception {
        Connection conn = null;
        CallableStatement cs = null;
        String str = "";
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{Call PK_FX.UP_DFHQ_SUREFH(?,?,?)}";
            cs = conn.prepareCall(sql);
            cs.setString(1, manId);
            cs.setString(2, runids);
            cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);
            cs.execute();
            str = cs.getString(3);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return str;
    }

    @Override
    public boolean qxDeapVerifyFunc(List<String> runIds) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "UPDATE FX_HAND_OUT O SET O.IS_VERIFY_BM = '0' ,O.IS_VERIFY_YW = '1' WHERE O.RUN_ID = ?";
            for (int i = 0; i < runIds.size(); i++) {
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, runIds.get(i));
                stmt.execute();
                conn.commit();
                stmt.close();
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return false;
    }

    @Override
    public boolean qxYwFH(String runIds) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        boolean flag = false;
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
//            String insql = "insert into fx_hand_out_xq select * from fx_hand_out f where f.run_id in" + runIds;
            String insql = "INSERT INTO fx_hand_out_xq " +
                           " (C_ID,H_ID,H_PRICE,H_AMOUNT_YF,H_AMOUNT_YW,IS_VERIFY_YW,VERIFY_DATE_YW,ZD_TYPE,S_ID,SOURCE_ID,SOURCE_ITEM_ID, " +
                           " STATION_ID,LAST_SEND_DATE,C_ZD_ID,SHELF_NUM,RUN_ID,H_AMOUNT_BM,IS_VERIFY_BM,VERIFY_DATE_BM,SEND_ADDRESS,SOURCE_CODE, " +
                           " MEMO,H_DISCOUNT,ADDRESS,SOURCE_DJTYPE) " +
                           "SELECT " +
                           " C_ID,H_ID,H_PRICE,H_AMOUNT_YF,H_AMOUNT_YW,IS_VERIFY_YW,VERIFY_DATE_YW,ZD_TYPE,S_ID,SOURCE_ID,SOURCE_ITEM_ID, " +
                           " STATION_ID,LAST_SEND_DATE,C_ZD_ID,SHELF_NUM,RUN_ID,H_AMOUNT_BM,IS_VERIFY_BM,VERIFY_DATE_BM,SEND_ADDRESS,SOURCE_CODE, " +
                           " MEMO,H_DISCOUNT,ADDRESS,SOURCE_DJTYPE " +
                           " FROM fx_hand_out f where f.run_id in " + runIds;
            flag = this.executeSQL(insql);
            if (flag) {
                String sql = "DELETE FROM  FX_HAND_OUT F WHERE F.RUN_ID in" + runIds;
                flag = this.executeSQL(sql);
            }
            flag = true;
        } catch (Exception e) {
            flag = false;
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return flag;
    }

    @Override
    public boolean sureKP(String kpId) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = " UPDATE FX_INVOICE_KP K SET K.SURE_INV ='1',K.KP_STATUS='1' WHERE K.INV_ID = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, kpId);
            stmt.execute();
            conn.commit();
            stmt.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return false;
    }

    @Override
    public boolean kpsqzt(String kpId) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = " UPDATE FX_INVOICE_KP K SET K.KP_STATUS ='1' WHERE K.INV_ID = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, kpId);
            stmt.execute();
            conn.commit();
            stmt.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return false;
    }

    @Override
    public String dkToYSFunc(String djId, String manId) throws Exception {
        Connection conn = null;
        CallableStatement cs = null;
        String str = "";
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{Call PK_FX_DZ.UP_BANKDKTOYS(?,?,?)}";
            cs = conn.prepareCall(sql);
            cs.setString(1, djId);
            cs.setString(2, manId);
            cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);
            cs.execute();
            str = cs.getString(3);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return str;
    }

    @Override
    public List moreAddByCId(String clientId) throws Exception {
        List list = new ArrayList();
        int count = 0;
        try {
            String asql = "SELECT s.c_address_send FROM bs_clients s WHERE s.clients_id = '" + clientId + "'";
            String sinId = this.jdbcTemplate.queryForObject(asql, String.class);
            String bsql = "SELECT COUNT(*) FROM MULTI_RECIEVER_ADDRESS S WHERE S.SIN_ID = '" + sinId + "' AND S.IS_SHOW = '1'";
            count = this.jdbcTemplate.queryForInt(bsql);
            if (count == 1) {
                String sql = "select * from MULTI_RECIEVER_ADDRESS S WHERE S.SIN_ID = '" + sinId + "' AND S.IS_SHOW = '1'";
                list = this.execSqlToList(sql);
            } else {
                list = new ArrayList();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * ***************************
     * 客户添货拉去客户的方法
     * *****************************
     */
    @Override
    public List getAddressGoodsByCId(String cId) throws Exception {
        List list = new ArrayList();
        int count = 0;
        try {
            String asql = "SELECT s.c_address_send FROM bs_clients s WHERE s.clients_id = '" + cId + "'";
            String sinId = this.jdbcTemplate.queryForObject(asql, String.class);
            String bsql = "SELECT COUNT(*) FROM MULTI_RECIEVER_ADDRESS S WHERE S.SIN_ID = '" + sinId + "' AND S.IS_SHOW = '1'";
            count = this.jdbcTemplate.queryForInt(bsql);
            if (count == 1) {
                String sql = "select * from MULTI_RECIEVER_ADDRESS S WHERE S.SIN_ID = '" + sinId + "' AND S.IS_SHOW = '1'";
                list = this.execSqlToList(sql);
            } else {
                list = new ArrayList();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * ***************************************
     * 银行到款拉取客户的方法
     * 3个返回参数用逗号隔开，1 客户ID  2 客户业务员  3 账号名
     * ***************************************
     */
    public List getDkAccountByClient(String bankaccount) throws Exception {
        List<String> list = new ArrayList<String>();
        Connection conn = null;
        CallableStatement cs = null;
        String str = "";
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{?=call PK_FX_DZ.UF_GETCLIENTDATABYBANKACCOUNT(?)}";
            cs = conn.prepareCall(sql);
            cs.registerOutParameter(1, Types.VARCHAR);
            cs.setString(2, bankaccount);
            cs.execute();
            str = cs.getString(1);
            String[] strs = str.split(",");
            for (String s : strs) {
                list.add(s);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return list;
    }

    /**
     * 客户收款,清除金额方法
     */
    public String removemoney(String chid) throws Exception {
        Connection conn = null;
        CallableStatement cs = null;
        String str = "";
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "DELETE FROM FX_SK_DK WHERE SK_ID=?";
            cs = conn.prepareCall(sql);
            cs.setString(1, chid);
            cs.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return str;
    }

    /**
     * 客户预收,清除金额方法
     */
    public String cjbremovemoney(String chid) throws Exception {
        Connection conn = null;
        CallableStatement cs = null;
        String str = "";
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "DELETE FROM FX_SK_YS WHERE SK_ID=?";
            cs = conn.prepareCall(sql);
            cs.setString(1, chid);
            cs.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return str;
    }

    /**
     * 包册数
     */
    @Override
    public List CountPackNum(String hid, String amount) throws Exception {
        List list = new ArrayList();
        Connection conn = null;
        CallableStatement cs = null;
        String str = "";
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{?=call pk_fx.Uf_GetPackNumByHid(?,?)}";
            cs = conn.prepareCall(sql);
            cs.registerOutParameter(1, Types.VARCHAR);
            cs.setString(2, hid);
            cs.setString(3, amount);
            cs.execute();
            str = cs.getString(1);
            list.add(str);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return list;
    }

    @Override
    public String getXtDiscount(String cid, String hId) throws Exception {
        String XtDiscount = "";
        Connection conn = null;
        CallableStatement cs = null;
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{?=call PK_FX_SAVE.uf_GetLastXSDiscount(?,?)}";
            cs = conn.prepareCall(sql);
            cs.registerOutParameter(1, Types.VARCHAR);
            cs.setString(2, hId);
            cs.setString(3, cid);
            cs.execute();
            XtDiscount = cs.getString(1);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return XtDiscount;
    }

    @Override
    public String yfdSaveBefore(List<Map<String, Object>> allRows,
                                HttpServletRequest request, HttpSession session) {
        String sql = "";
        Connection conn = null;
        CallableStatement cs = null;
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            //根据dsId 加载不同页面的列集合
            for (int i = 0; i < allRows.size(); i++) {
                String BW_ID = (String) allRows.get(i).get("BW_ID");
                String FJ_ID = (String) allRows.get(i).get("FJ_ID");
                String MONEY = (String) allRows.get(i).get("MONEY");
                String TFIDATE = "sysdate";
                sql = " insert into DS_YFMONEYINFO(BW_ID,FJ_ID,MONEY,TFIDATE) values('" + BW_ID + "','" + FJ_ID + "'," + MONEY + "," + TFIDATE + ")";
                cs = conn.prepareCall(sql);
                cs.execute();
                conn.commit();
            }
            return "success";
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                try {
                    cs.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return "fail";
    }

    @Override
    public String clientsureFH(String manId, String runids) throws Exception {
        Connection conn = null;
        CallableStatement cs = null;
        String str = "";
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{Call PK_FX.UP_DFHQ_CLIENTSUREFH(?,?,?)}";
            cs = conn.prepareCall(sql);
            cs.setString(1, manId);
            cs.setString(2, runids);
            cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);
            cs.execute();
            str = cs.getString(3);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return str;
    }

    @Override
    public List DKMByClient(String clientId) throws Exception {
        List list = new ArrayList();
        try {
            String sql = "SELECT nvl(sum((D.DK_MONEY - PK_FX_DZ.UF_GETDKSYMONEY(D.DK_ID))),0) DKMONEY FROM FX_BANK_DAOKUAN D WHERE D.CLIENT_ID='" + clientId + "' and d.is_verify='1'";
            list = this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List getcontractnobyzcid(String mb) {
        List list = new ArrayList();
        try {
            String sql = " SELECT * FROM vv_yz_yflqhm temp where temp.man_business = '" + mb + "'";
            list = this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    /**
     * 出版社预收,清除金额方法
     */
    public String cbsremovemoney(String chid) throws Exception {
        Connection conn = null;
        CallableStatement cs = null;
        String str = "";
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "DELETE FROM CW_CBS_YS WHERE SK_ID=?";
            cs = conn.prepareCall(sql);
            cs.setString(1, chid);
            cs.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return str;
    }

    /**
     * 出版社预收保存方法
     */
    @Override


    public boolean saveCBSDZYSFunc(String skId, String YSID, String PUB_ID, List<Map<String, String>> ysData, String manId)
            throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "insert into CW_CBS_YS(dz_ys_id,sk_id,ys_id,PUB_ID,ys_money_occupy,input_man,input_date)" +
                    "values(?,?,?,?,?,'" + manId + "',?)";
            String sqlUpd = "UPDATE CW_CBS_YS SET YS_MONEY_OCCUPY = ? WHERE SK_ID = ? AND YS_ID = ?";
            int countNum = 0;
            String selSql = "";
            if (!"".equals(skId) && skId != null) {
                for (int i = 0; i < ysData.size(); i++) {
                    selSql = "SELECT COUNT(1) FROM CW_CBS_YS D WHERE D.SK_ID = '" + skId + "' AND D.YS_ID = '" + ysData.get(i).get("ysId") + "'";


                    countNum = this.getJdbcTemplate().queryForInt(selSql);
                    if (countNum > 0) {
                        stmt = conn.prepareStatement(sqlUpd);
                        stmt.setString(1, ysData.get(i).get("useMoney"));
                        stmt.setString(2, skId);
                        stmt.setString(3, ysData.get(i).get("ysId"));
                        stmt.execute();
                        stmt.close();
                    } else {
                        stmt = conn.prepareStatement(sql);
                        stmt.setString(1, UUID32.newGuid());
                        stmt.setString(2, skId);
                        stmt.setString(3, ysData.get(i).get("ysId"));
                        stmt.setString(4, PUB_ID);
                        stmt.setString(5, ysData.get(i).get("useMoney"));
                        stmt.setDate(6, java.sql.Date.valueOf(DateTimeStr.getDateTime()));
                        stmt.execute();
                        stmt.execute("commit");
                        conn.commit();
                        stmt.close();
                    }
                }
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return false;
    }


    @Override
    public List getcontractnobycbsid(String mb) {

        List list = new ArrayList();
        try {
            String sql = "   select * from BS_PUBLISHER bp left join MULTI_BANK_INFO mb on  mb.sin_id = bp.card_no  where bp.pub_id = '" + mb + "'";
            list = this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    @Override
    public List getcontractnobyfkdxid(String VENDOR_ID) {
        List list = new ArrayList();
        try {
            String sql = "select * from zc_authorcheck za where za.is_verify='1' and za.is_destroy='0' and za.dj_code not in ( select cai.dj_code from cw_authorinvoice_item cai left join  cw_authorinvoice ca  on  ca.zzfpd_id=cai.zzfpd_id where ca.is_destroy='0') and za.pay_party = '" + VENDOR_ID + "'";
            list = this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    /**
     * 根据付款对象id，拉对对应的 所有已对账未收票,未被使用的 作者对账单    (作者核销单使用)
     *
     * @param VENDOR_ID
     * @param request
     * @return
     */

    public List getcontractnobyzzfkdxid(String VENDOR_ID) {
        List list = new ArrayList();
        try {
            String sql = "select * from zc_authorcheck za where za.is_verify = '1' and za.is_destroy = '0' and za.dj_code not in (select cai.dj_code from zc_authorverification_item cai left join zc_authorverification ca on ca.zzhxd_id = cai.zzhxd_id where ca.is_destroy = '0') and za.pay_party = '" + VENDOR_ID + "'";
            list = this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;

    }

    public List getcontractnobyzzfkdxid(String hxdId, java.util.Date begin, java.util.Date end, String XT_ID, String VENDOR_ID, String AUTHOR) {
        List list = new ArrayList();
        try {
            String sql = "SELECT za.*, (select wm_concat(NUMBER_PRINT) from (select item.NUMBER_PRINT from ZC_AUTHORCHECK_ITEM item WHERE item.CWZZ_ID = za.CWZZ_ID ORDER BY NUMBER_PRINT)) prints\n" +
                    "FROM zc_authorcheck za, BJ_XT xt\n" +
                    "WHERE za.is_verify = '1'\n" +
                    "      AND za.is_destroy = '0'\n" +
                    "      AND za.dj_code NOT IN (SELECT cai.dj_code\n" +
                    "                              FROM zc_authorverification_item cai LEFT JOIN\n" +
                    "                                zc_authorverification ca\n" +
                    "                                  ON ca.zzhxd_id = cai.zzhxd_id\n" +
                    "                              WHERE ca.is_destroy = '0' ";
            if(StringUtils.isNotBlank(hxdId)) {
                sql += "  AND ca.ZZHXD_ID != '" + hxdId + "'\n";
            }
            sql += ")      AND za.TOPIC_SELECTED = xt.XT_ID\n";

            if(StringUtils.isNotBlank(VENDOR_ID)) {
                sql += "      AND za.pay_party = '" + VENDOR_ID + "'";
            }
            if(StringUtils.isNotBlank(AUTHOR)) {
                sql += "      AND xt.AUTHOR = '" + AUTHOR + "'";
            }
            if(StringUtils.isNotBlank(XT_ID)){
                String[] ids = XT_ID.split(";");
                String idsStr = "";
                for(int i=0;i<ids.length;i++) {
                    if(StringUtils.isNotBlank(ids[i])) {
                        idsStr += "'" + ids[i] + "',";
                    }
                }
                idsStr = idsStr.substring(0, idsStr.length() - 1);
                sql += "      AND xt.XT_ID in (" + idsStr + ")";
            }
            if(null != begin && null != end) {
                sql += "      and to_date(to_char(za.DZ_DATE, 'yyyy-MM'), 'yyyy-MM') BETWEEN to_date(to_char(?, 'yyyy-MM'), 'yyyy-MM') AND to_date(to_char(?, 'yyyy-MM'), 'yyyy-MM') ORDER BY za.TOPIC_SELECTED, za.DZ_DATE DESC ";
                list = this.jdbcTemplate.queryForList(sql,begin, end);
            } else if (null != begin) {
                sql += "      and to_date(to_char(za.DZ_DATE, 'yyyy-MM'), 'yyyy-MM') >= to_date(to_char(?, 'yyyy-MM'), 'yyyy-MM') ORDER BY za.TOPIC_SELECTED, za.DZ_DATE DESC ";
                list = this.jdbcTemplate.queryForList(sql,begin);
            } else if (null != end) {
                sql += "      and to_date(to_char(za.DZ_DATE, 'yyyy-MM'), 'yyyy-MM') <= to_date(to_char(?, 'yyyy-MM'), 'yyyy-MM') ORDER BY za.TOPIC_SELECTED, za.DZ_DATE DESC ";
                list = this.jdbcTemplate.queryForList(sql,end);
            } else {
                sql += " ORDER BY za.TOPIC_SELECTED, za.DZ_DATE DESC ";
                list = this.jdbcTemplate.queryForList(sql);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;

    }

    /**
     * (作者核销单使用)
     *
     * @param selRows
     * @param skid
     * @return
     * @throws Exception
     */
    @Override
    public boolean saveCacheYF(List<Map<String, String>> selRows, String skid) throws Exception {
        String uuid = UUID32.newGuid();
        boolean flag = false;
        List<Object[]> list = null;
        Date date = new Date(0);
        try {
            String sql = "DELETE FROM fx_sk_dk_cache";
            flag = this.executeSQL(sql);
            if (flag) {
                String sql1 = "INSERT INTO fx_sk_dk_cache(SK_DK_ID,DK_ID,SK_ID,DK_MONEY_USE,INPUT_MAN,INPUT_DATE,DK_MONEY_OCCUPY) VALUES(sys_guid(),?,?,?,?,?,?)";
                list = new ArrayList<Object[]>();
                for (int i = 0; i < selRows.size(); i++) {
                    list.add(new Object[]{selRows.get(i).get("DK_ID"), skid, selRows.get(i).get("USE_MONEY_TX"), uuid, date, selRows.get(i).get("USE_MONEY_TX")});
                }
                int[] lengthF = this.jdbcTemplate.batchUpdate(sql1, list);
                if (lengthF.length == selRows.size()) {
                    flag = true;
                } else {
                    flag = false;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }


    @Override
    public List findYFData(String skId, String clientId) throws Exception {
        List list = new ArrayList();
        try {
            String sql = "SELECT * FROM BJ_ZZYFK s where s.YFK_ID = '" + skId + "' and s.INTERPRETER_MAN = '" + clientId + "'";
            list = this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    /**
     * 根据付款对象，查询预付金额之和   （所有的可用金额）
     *
     * @param JIAFANG 付款对象
     * @return
     * @throws Exception
     */

    public double getzzyfdSum_yfmoney(String dj_code) throws Exception {
        double kymoney = 0;
        try {
//            //获得该作者所有的预付金额
//            String storeSql = "select sum(z.prepaid_amount) from zz_receipt z where z.is_verify='1' and z.is_destroy='0' and z.interpreter_man= '" + JIAFANG + "'";
//            double yfmoney = this.getJdbcTemplate().queryForInt(storeSql);
//            //获得该作者所有的占用金额
//            String zySql = "select sum(c.yf_money_occupy) from BJ_ZZYFK  c where c.interpreter_man='" + JIAFANG + "'";
//            double zymoney = this.getJdbcTemplate().queryForInt(zySql);
////            //获得该作者所有的已使用金额
////            String sySql ="select sum(c.yf_money_use) from BJ_ZZYFK  c where c.interpreter_man='"+JIAFANG+"'";
////            double  symoney = this.getJdbcTemplate().queryForInt(sySql);
//
////            kymoney = yfmoney -  zymoney - symoney;


            String zySql = " select  (PK_FX_DZ.UP_GETZZHXD_ZONGYUFUYUE(c.pay_party)) use_prepay from zc_authorverification  c where c.dj_code='" + dj_code + "'";
            kymoney = this.getJdbcTemplate().queryForObject(zySql,Double.class);
//            kymoney = this.getJdbcTemplate().queryForInt(zySql);

        } catch (Exception e) {
            e.printStackTrace();
        }
        return kymoney;
    }

    @Override
    public double saveYFFunc(String skId, String cid, String djcode, List<Map<String, String>> dkData,
                           String manId) throws Exception {
        Connection conn = null;
        CallableStatement cs=null;
        PreparedStatement stmt = null;
        double sym=0;
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "INSERT INTO BJ_ZZYFK(YFK_ID,YF_ID,YF_MONEY_OCCUPY,INPUT_MAN,INPUT_DATE,INTERPRETER_MAN) " +
                    "VALUES(?,?,?,'" + manId + "',?,?)";
            String sqlUpd = "UPDATE BJ_ZZYFK SET YF_MONEY_OCCUPY = ? WHERE YF_ID = ? and YFK_ID=? ";
            int countNum = 0;
            String selSql = "";
            double USE_MONEY_TX = 0;    //本单输入的金额
            double YF_MONEY = 0;    //主表的预付余额
            double YF_USE_MONEY = 0;  //所有的已使用金额
            for (int i = 0; i < dkData.size(); i++) {
                USE_MONEY_TX += Double.parseDouble((dkData.get(i).get("USE_MONEY_TX")) != null ? (dkData.get(i).get("USE_MONEY_TX")) : "0");     //本次输入的金额之和
                YF_MONEY += Double.parseDouble((dkData.get(i).get("YF_MONEY")) != null ? (dkData.get(i).get("YF_MONEY")) : "0");                 //预付单金额之和
                YF_USE_MONEY += Double.parseDouble((dkData.get(i).get("YF_USE_MONEY")) != null ? (dkData.get(i).get("YF_USE_MONEY")) : "0");     //已使用金额之和
                selSql = "SELECT COUNT(1) FROM BJ_ZZYFK D WHERE D.YF_ID ='" + dkData.get(i).get("CL_ID") + "' and D.YFK_ID='" + djcode + "' ";
                countNum = this.getJdbcTemplate().queryForInt(selSql);
                if (countNum > 0) {
                    stmt = conn.prepareStatement(sqlUpd);
                    // 如果本单输入的金额为0，则吧本次的占用金额放在，第三张表中，否则则吧本单输入金额，放进占用金额中
                    if (dkData.get(i).get("USE_MONEY_TX").equals("0")) {
                        stmt.setString(1, dkData.get(i).get("YF_MONEY_LOCK"));
                    } else {
                        stmt.setString(1, dkData.get(i).get("USE_MONEY_TX"));     //
                    }
                    stmt.setString(2, dkData.get(i).get("CL_ID"));
                    stmt.setString(3, djcode);
                    stmt.execute();
                    stmt.close();
                } else {
                    stmt = conn.prepareStatement(sql);
                    stmt.setString(1, djcode);
                    stmt.setString(2, dkData.get(i).get("CL_ID"));
                    stmt.setString(3, dkData.get(i).get("USE_MONEY_TX"));
                    stmt.setDate(4, java.sql.Date.valueOf(DateTimeStr.getDateTime()));
                    stmt.setString(5, cid);
                    stmt.execute();
                    stmt.execute("commit");
                    conn.commit();
                    stmt.close();

                }

//					//修改预收表
//					stmt = conn.prepareStatement(updSql);
//					stmt.setFloat(1,Float.parseFloat(ysData.get(i).get("useMoney")));
//					stmt.setString(2, ysData.get(i).get("ysId"));
//					stmt.execute();
//					stmt.close();
            }
            //如果输入的本单金额大于零，则把金额放入主表的使用预付中
            if (USE_MONEY_TX > 0) {
                double BALANCE_PREPAY = YF_MONEY - YF_USE_MONEY - USE_MONEY_TX;   //计算预付余额 = 所有的预付金额 - 已使用金额 - 本单的占用金额
                String zbSql = "UPDATE zc_authorverification c SET c.use_prepay_hs=" + USE_MONEY_TX + ",c.balance_prepay=" + BALANCE_PREPAY + " where c.dj_code=?";
                stmt = conn.prepareStatement(zbSql);
                stmt.setString(1, djcode);
                stmt.execute();
                stmt.close();
            }


            String zySql = " select  (PK_FX_DZ.UP_GETZZHXD_MONEYOCCUPY(c.dj_code)) use_prepay from zc_authorverification  c where c.dj_code='" + djcode + "'";
            sym = this.getJdbcTemplate().queryForObject(zySql,Double.class);
//            sym = this.getJdbcTemplate().queryForInt(zySql);


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if(cs!=null){
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return sym;
    }

    /**
     * 使用预付,清除金额方法
     */
    public String zzhxdremovemoney(String djcode) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        String str = "";
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
                String sql = "delete BJ_ZZYFK c where c.yfk_id=?";
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, djcode);
                stmt.execute();
                stmt.close();
                str = "T";
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return str;
    }




    /**
     * 判断弹框界面的预付单，是否要清楚数据   (作者核销单)
     *
     * @param chid
     * @return
     * @throws Exception
     */
    @Override
    public int selremove(String djcode, String YF_ID) throws Exception {

        String sySql = "SELECT COUNT(1) FROM BJ_ZZYFK D WHERE D.YFK_ID = '" + djcode + "' and D.YF_ID = '" + YF_ID + "'";
        int countNum = this.getJdbcTemplate().queryForInt(sySql);

        return countNum;
    }

    /**
     * 费用报销,清除金额方法
     */
    public String bxfyremovemoney(String chid) throws Exception {
        Connection conn = null;
        CallableStatement cs = null;
        String str = "";
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "DELETE FROM CW_BXYXYS WHERE BXDJ_ID=?";
            cs = conn.prepareCall(sql);
            cs.setString(1, chid);
            cs.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                cs.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return str;
    }


    /**
     * 报销费用保存方法
     */
    @Override
    public boolean saveBxfyYSFunc(String skId, String YSID, String XT_ID, List<Map<String, String>> ysData, String manId)
            throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "insert into CW_BXYXYS(BXYXYS_ID,BXDJ_ID,YXYS_ID,YXYSDJ_ID,BX_MONEY_OCCUPY,input_man,input_date)" +
                    "values(?,?,?,?,?,'" + manId + "',?)";
            String sqlUpd = "UPDATE CW_BXYXYS SET BX_MONEY_OCCUPY = ? WHERE YXYS_ID = ? AND BXDJ_ID = ?";
            int countNum = 0;
            String selSql = "";
            if (!"".equals(skId) && skId != null) {
                for (int i = 0; i < ysData.size(); i++) {
                    selSql = "SELECT COUNT(1) FROM CW_BXYXYS D WHERE D.YXYS_ID = '" + skId + "' AND D.BXDJ_ID = '" + ysData.get(i).get("ysId") + "'";


                    countNum = this.getJdbcTemplate().queryForInt(selSql);
                    if (countNum > 0) {
                        stmt = conn.prepareStatement(sqlUpd);
                        stmt.setString(1, ysData.get(i).get("useMoney"));
                        stmt.setString(2, skId);
                        stmt.setString(3, ysData.get(i).get("ysId"));
                        stmt.execute();
                        stmt.close();
                    } else {
                        stmt = conn.prepareStatement(sql);
                        stmt.setString(1, UUID32.newGuid());
                        stmt.setString(2, skId);
                        stmt.setString(3, ysData.get(i).get("ysId"));
                        stmt.setString(4, XT_ID);
                        stmt.setString(5, ysData.get(i).get("useMoney"));
                        stmt.setDate(6, java.sql.Date.valueOf(DateTimeStr.getDateTime()));
                        stmt.execute();
                        stmt.execute("commit");
                        conn.commit();
                        stmt.close();
                    }
                }
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return false;
    }

    @Override
    public int getYINSHUsum(String VENDOR_ID,String YINCI) throws Exception{
        int zyinshu = 0;
        try{
            String sql = "select sum(c.pro_duty_amount) from  yz_print_explain c where  c.is_destroy='0' and  c.xt_id='" + VENDOR_ID + "' and c.yinci='"+YINCI+"' ";
            zyinshu = this.getJdbcTemplate().queryForInt(sql);


        }catch (Exception e){
            e.printStackTrace();
        }

        return zyinshu;
    }

    @Override
    public List getcontractnobyxtid(String VENDOR_ID) {
        List list = new ArrayList();
        try {
            String sql = "select * from BJ_ISBN_CIP  where  xt_id = '" + VENDOR_ID + "'";
            list = this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    /**
     *  (客户回款计划)
     * @param VENDOR_ID    片区经理
     * @param request
     * @return
     */
    @Override
    public List getcontractnobykhhkjh(String VENDOR_ID,String KH_DATE) throws Exception{
        List<Map<String,Object>> list = new ArrayList();
        List<Map<String,Object>> maxlist = new ArrayList();    //最后返回的集合
        String lsTime;     //账期时间
        String upTime;     //上月时间
        String bTime;      //本月时间
        String nxTime;     //下月时间
        SimpleDateFormat myFmt=new SimpleDateFormat("yyyy-MM");

        try {
            String sql ="SELECT bc.clients_id,bc.CLIENTS_NAME,nvl(bc.PROVINCE_ID,' ') " +
                    "PROVINCE_ID,nvl(bc.CC_ID ,' ') CC_ID,nvl(bc.DAY_ZT,0) DAY_ZT "  +
                    "FROM BS_CLIENTS bc  where bc.O_ID_LASTGR = '"+VENDOR_ID+"'  and bc.is_exist='1'group by bc.clients_id,bc.CLIENTS_NAME,bc.PROVINCE_ID,bc.CC_ID,bc.DAY_ZT";
            list = this.jdbcTemplate.queryForList(sql);
        }catch (Exception e){
            e.printStackTrace();
        }

        maxlist.addAll(list);

        for (int i =0;i<list.size();i++){
            System.out.print(list.get(i).get("DAY_ZT"));
            BigDecimal DAY_ZT = (BigDecimal)list.get(i).get("DAY_ZT");
            if(DAY_ZT == null) continue;
            try {
                System.out.println("=============================================");
                //把传入进来的对账时间转换成具体月份
                java.util.Date date = myFmt.parse(KH_DATE);
                System.out.println("转换后的时间"+date);
                //加载日历,计算出 算上账期 的月份
                Calendar rightNow = Calendar.getInstance();
                rightNow.setTime(date);
                rightNow.add(Calendar.MONTH,-(((BigDecimal)list.get(i).get("DAY_ZT")).intValue()));
                lsTime =myFmt.format(rightNow.getTime());
                System.out.println("加上账期后的本月时间:"+lsTime);

                Calendar nxmonth = Calendar.getInstance();
                nxmonth.setTime(date);
                nxmonth.add(Calendar.MONTH,-(((BigDecimal)list.get(i).get("DAY_ZT")).intValue())+1);
                nxTime =myFmt.format(nxmonth.getTime());
                System.out.println("加上账期后下月的时间:"+nxTime);


                Calendar upNow = Calendar.getInstance();
                upNow.setTime(date);
                upNow.add(Calendar.MONTH,-1);
                upTime =myFmt.format(upNow.getTime());
                System.out.println("上月的时间:"+upTime);


                Calendar month = Calendar.getInstance();
                month.setTime(date);
                month.add(Calendar.MONTH,0);
                bTime =myFmt.format(month.getTime());
                System.out.println("本月的时间:"+bTime);



                System.out.println("=============================================");

                /**
                 * 历史欠款   字段   HISTORY_DEPT
                  */
                //销售单的账期到期日前的所有已出库未核销的实洋金额
                String xsdsql = "select nvl(sum(vk.real_money),0) from vv_khhkjh_forlist vk where  vk.dj_type='销售单' and vk.clients_id=(SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1') " +
                        "and vk.px_code not in(SELECT KPI.Dj_Code  FROM FX_CHARGE_ITEM KPI inner join FX_CHARGE FC ON KPI.CH_ID=FC.CH_ID AND FC.IS_VERIFY='1' AND FC.CLIENT_ID= (SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1')) " +
                        "and ( to_char(vk.input_date,'yyyy-mm')) < '"+lsTime+"'";
                double xsdHISTORY_DEPT = this.jdbcTemplate.queryForObject(xsdsql,Double.class);

                // 销售退货 已出库未核销的实洋金额
                String xsthsql = "select nvl(sum(vk.real_money),0) from vv_khhkjh_forlist vk where  vk.dj_type='销售退货'  AND vk.clients_id=(SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1')\n" +
                        "and vk.px_code not in(SELECT KPI.Dj_Code  FROM FX_CHARGE_ITEM KPI inner join FX_CHARGE FC ON KPI.CH_ID=FC.CH_ID AND FC.IS_VERIFY='1' AND FC.CLIENT_ID= (SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1'))\n" +
                        "and ( to_char(vk.input_date,'yyyy-mm')) < '"+bTime+"'";
                double xsthHISTORY_DEPT = this.jdbcTemplate.queryForObject(xsthsql,Double.class);


                //  客户费用单的已审核未核销的实洋金额
                String fydsql ="select nvl(sum(vk.real_money),0) from vv_khhkjh_forlist vk where  vk.dj_type='客户费用' AND vk.clients_id=(SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1') \n" +
                        "and vk.px_code not in(SELECT KPI.Dj_Code  FROM FX_CHARGE_ITEM KPI inner join FX_CHARGE FC ON KPI.CH_ID=FC.CH_ID AND FC.IS_VERIFY='1' AND FC.CLIENT_ID= (SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1'))\n" +
                        "and ( to_char(vk.input_date,'yyyy-mm')) < '"+bTime+"'";
                double fydHISTORY_DEPT = this.jdbcTemplate.queryForObject(fydsql,Double.class);


                // 该客户已审核的银行到款日期为上月底前的可用金额和
                String yhdksql="SELECT nvl(sum((D.DK_MONEY - PK_FX_DZ.UF_GETDKSYMONEY(D.DK_ID))),0) SY_MONEY FROM FX_BANK_DAOKUAN D WHERE D.IS_VERIFY='1' \n" +
                        "AND D.CLIENT_ID=(SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1')  and ( to_char(D.DK_DATE,'yyyy-mm')) <= '"+upTime+"'";
                double yhdkHISTORY_DEPT = this.jdbcTemplate.queryForObject(yhdksql,Double.class);

                // 该客户的客户预收 （非保证金类型）审核日期为上月底前的可用金额和
                String kgyssql="SELECT NVL(sum((NVL(FY.YS_MONEY, 0) - NVL(PK_FX_DZ.UF_GETYSSYMONEY(FY.YS_ID), 0) - NVL(PK_FX_DZ.UF_GETYSZYMONEY(FY.YS_ID), 0))),0) YS_MONEY_KY\n" +
                        "  FROM FX_YS FY WHERE FY.IS_VERIFY='1' AND FY.DJ_TYPE !='ys_type.001' AND FY.CLIENT_ID=(SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1') \n" +
                        "and ( to_char(FY.Verify_Date,'yyyy-mm')) <= '"+upTime+"' ";
                double kgysHISTORY_DEPT = this.jdbcTemplate.queryForObject(kgyssql,Double.class);



                //计算  历史欠款 = 销售单金额 + 退货单金额 + 费用单金额 - 银行到款金额 - 客户预收（非保证金类型）
                double HISTORY_DEPT = xsdHISTORY_DEPT + xsthHISTORY_DEPT +fydHISTORY_DEPT - yhdkHISTORY_DEPT - kgysHISTORY_DEPT;

                maxlist.get(i).put("HISTORY_DEPT",HISTORY_DEPT);


                /**
                 * 本月应收  字段  RECEIVABL_THIS    (算上账期的本月)
                 */
                //销售单的账期到期日 所在月 的所有已出库未核销的实洋金额
                String byysSql = "select nvl(sum(vk.real_money),0) from vv_khhkjh_forlist vk where  vk.dj_type='销售单' and vk.clients_id=(SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1') " +
                        "and vk.px_code not in(SELECT KPI.Dj_Code  FROM FX_CHARGE_ITEM KPI inner join FX_CHARGE FC ON KPI.CH_ID=FC.CH_ID AND FC.IS_VERIFY='1' AND FC.CLIENT_ID= (SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1')) " +
                        "and ( to_char(vk.input_date,'yyyy-mm')) = '"+lsTime+"'";
                double RECEIVABL_THIS = this.jdbcTemplate.queryForObject(byysSql,Double.class);

                maxlist.get(i).put("RECEIVABL_THIS",RECEIVABL_THIS);


                /**
                 * 下月应收  字段  RECEIVABL_NEXT   (算上账期的下月)
                 */
                //销售单的账期到期日 所在月 的所有已出库未核销的实洋金额
                String xyysSql = "select nvl(sum(vk.real_money),0) from vv_khhkjh_forlist vk where  vk.dj_type='销售单' and vk.clients_id=(SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1') " +
                        "and vk.px_code not in(SELECT KPI.Dj_Code  FROM FX_CHARGE_ITEM KPI inner join FX_CHARGE FC ON KPI.CH_ID=FC.CH_ID AND FC.IS_VERIFY='1' AND FC.CLIENT_ID= (SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1')) " +
                        "and ( to_char(vk.input_date,'yyyy-mm')) = '"+nxTime+"'";
                double RECEIVABL_NEXT = this.jdbcTemplate.queryForObject(xyysSql,Double.class);

                maxlist.get(i).put("RECEIVABL_NEXT",RECEIVABL_NEXT);


                /**
                 *  本月实际应结  字段  PAY_SHOULD
                  */
                //    本月实际应结 = 历史欠款 + 本月应收
                double PAY_SHOULD = HISTORY_DEPT + RECEIVABL_THIS ;

                maxlist.get(i).put("PAY_SHOULD",PAY_SHOULD);

                /**
                 *  总欠金额  字段  SUM_DEPT
                 *  取值逻辑修改于20180227 具体修改代码查看Git日志
                 */
                //对账日期的前一个月，该客户的 总销售金额+总退货金额+总费用金额-银行到款余额 - 客户预收余额(非保证金)的值， 即 现有的总欠

                //销售单的账期到期日前的所有已出库未核销的实洋金额
                String xsdqksql = "select nvl(sum(vk.real_money),0) from vv_khhkjh_forlist vk where  vk.dj_type='销售单' and vk.clients_id=(SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1') " +
//                        "and vk.px_code not in(SELECT KPI.Dj_Code  FROM FX_CHARGE_ITEM KPI inner join FX_CHARGE FC ON KPI.CH_ID=FC.CH_ID AND FC.IS_VERIFY='1' AND FC.CLIENT_ID= (SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1')) " +
                        "and ( to_char(vk.input_date,'yyyy-mm')) <= '"+upTime+"'";
                double xsdSUM_DEPT = this.jdbcTemplate.queryForObject(xsdqksql,Double.class);

                // 销售退货 已出库未核销的实洋金额
                String xsthqksql = "select nvl(sum(vk.real_money),0) from vv_khhkjh_forlist vk where  vk.dj_type='销售退货'  AND vk.clients_id=(SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1')\n" +
//                        "and vk.px_code not in(SELECT KPI.Dj_Code  FROM FX_CHARGE_ITEM KPI inner join FX_CHARGE FC ON KPI.CH_ID=FC.CH_ID AND FC.IS_VERIFY='1' AND FC.CLIENT_ID= (SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1'))\n" +
                        "and ( to_char(vk.input_date,'yyyy-mm')) <= '"+upTime+"'";
                double xsthSUM_DEPT = this.jdbcTemplate.queryForObject(xsthqksql,Double.class);


                //  客户费用单的已审核未核销的实洋金额
                String fydqksql ="select nvl(sum(vk.real_money),0) from vv_khhkjh_forlist vk where  vk.dj_type='客户费用' AND vk.clients_id=(SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1') \n" +
//                        "and vk.px_code not in(SELECT KPI.Dj_Code  FROM FX_CHARGE_ITEM KPI inner join FX_CHARGE FC ON KPI.CH_ID=FC.CH_ID AND FC.IS_VERIFY='1' AND FC.CLIENT_ID= (SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1'))\n" +
                        "and ( to_char(vk.input_date,'yyyy-mm')) <= '"+upTime+"'";
                double fydSUM_DEPT = this.jdbcTemplate.queryForObject(fydqksql,Double.class);


                //改户已审核的银行到款日期为上月底前的可用金额和
//                String yhdkqksql="SELECT nvl(sum((D.DK_MONEY - PK_FX_DZ.UF_GETDKSYMONEY(D.DK_ID))),0) SY_MONEY FROM FX_BANK_DAOKUAN D WHERE D.IS_VERIFY='1' \n" +
                String yhdkqksql="SELECT nvl(sum(D.DK_MONEY),0) SY_MONEY FROM FX_BANK_DAOKUAN D WHERE D.IS_VERIFY='1' and D.IS_DESTROY = 0 \n" +
                        "AND D.CLIENT_ID=(SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1')  and ( to_char(D.DK_DATE,'yyyy-mm')) <= '"+upTime+"'";
                double yhdkSUM_DEPT = this.jdbcTemplate.queryForObject(yhdkqksql,Double.class);

                //该户的客户预收 （非保证金类型）审核日期为上月底前的可用金额和
//                String kgysqksql="SELECT NVL(sum((NVL(FY.YS_MONEY, 0) - NVL(PK_FX_DZ.UF_GETYSSYMONEY(FY.YS_ID), 0) - NVL(PK_FX_DZ.UF_GETYSZYMONEY(FY.YS_ID), 0))),0) YS_MONEY_KY\n" +
                String kgysqksql="SELECT NVL(sum((NVL(FY.YS_MONEY, 0))),0) YS_MONEY_KY\n" +
                        "  FROM FX_YS FY WHERE FY.IS_VERIFY='1' AND FY.IS_DESTROY = 0 AND FY.DJ_TYPE !='ys_type.001' AND FY.CLIENT_ID=(SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1') \n" +
                        "and ( to_char(FY.Verify_Date,'yyyy-mm')) <= '"+upTime+"' ";
                double kgysSUM_DEPT = this.jdbcTemplate.queryForObject(kgysqksql,Double.class);

                //该户的客户预收 多减得金额（该金额由银行到款单自动生成）
                String xtscyhdksql="SELECT NVL(sum((NVL(FY.YS_MONEY, 0))),0) YS_MONEY_KY\n" +
                        "  FROM FX_YS FY WHERE FY.IS_VERIFY='1' AND FY.IS_DESTROY = 0 AND FY.MEMO LIKE '由银行到款单DK%' AND FY.DJ_TYPE !='ys_type.001' AND FY.CLIENT_ID=(SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1') \n" +
                        "and ( to_char(FY.Verify_Date,'yyyy-mm')) <= '"+upTime+"' ";
                double khysByyhdkSUM_DEPT = this.jdbcTemplate.queryForObject(xtscyhdksql,Double.class);

                String clientBondSql = "select BOND from bs_client_bond_config where client_id = (SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1')";
                double clientBond = 0 ;
                try {
                    clientBond = this.jdbcTemplate.queryForObject(clientBondSql, Double.class);
                } catch (Exception e) {}

                //计算  总欠金额 = 销售单金额 + 退货单金额 + 费用单金额 - 银行到款金额(银行到款日期) - 客户预收（非保证金类型）+ 客户预收多减得金额(该金额由银行到款单自动生成) + 客户保证金
                double SUM_DEPT = xsdSUM_DEPT + xsthSUM_DEPT +fydSUM_DEPT - yhdkSUM_DEPT - kgysSUM_DEPT + khysByyhdkSUM_DEPT + clientBond;

                maxlist.get(i).put("SUM_DEPT",SUM_DEPT);


                /**
                 *  本月实际回款 字段  RETURN_MONEY
                 */

                // 银行   到款日期    本月已审核的金额和
                String sjhksql ="SELECT nvl(sum(D.DK_MONEY),0) SY_MONEY FROM FX_BANK_DAOKUAN D WHERE D.IS_VERIFY='1' " +
                        " AND D.CLIENT_ID=(SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1')  and ( to_char(D.DK_DATE,'yyyy-mm')) = '"+bTime+"'";
                double RETURN_MONEY = this.jdbcTemplate.queryForObject(sjhksql,Double.class);

                maxlist.get(i).put("RETURN_MONEY",RETURN_MONEY);


                /**
                 * 上月退款  字段  LAST_RETURN
                 */
                //上月，该客户的总退货实洋，和账期无关
                String sytksql = "select nvl(sum(vk.real_money),0) from vv_khhkjh_forlist vk where  vk.dj_type='销售退货'  AND vk.clients_id=(SELECT CLIENTS_ID FROM BS_CLIENTS b where b.clients_name = '"+list.get(i).get("CLIENTS_NAME")+"' and b.is_exist='1')\n" +
                        "and ( to_char(vk.input_date,'yyyy-mm')) = '"+upTime+"'";
                double LAST_RETURN = this.jdbcTemplate.queryForObject(sytksql,Double.class);

                maxlist.get(i).put("LAST_RETURN",LAST_RETURN);


            } catch (Exception e) {
                e.printStackTrace();
            }

        }

        return maxlist;
    }

    @Override
    public DataStore getDataStoreById(String dsid) throws Exception {
        String hql = " from DataStore ds where ds.dsId = ?";
        Query query = sessionFactory.getCurrentSession().createQuery(hql);
        query.setString(0, dsid);
        List<DataStore> list = query.list();
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    /*
    * 客户实销上报统计（按日）
    * 20170514
    * ,String date2,String date3
    * */
    @Override

    public QueryJson loadclientbyData(DataStore dsDataStore,DataStore dsDataStoreTotal,String date1) throws Exception {
        QueryJson queryJson=new QueryJson();
        List dataList=new ArrayList();
        String sql="";
        String sqlTotal="";
        if(dsDataStore!=null){
            sql=dsDataStore.getSqlText();
            sql=sql.replace("'$date1'","'"+date1+"'");
//            sql=sql.replace("'$date2'","'"+date2+"'");
//            sql=sql.replace("'$date3'","'"+date3+"'");
        }
        if(dsDataStoreTotal!=null){
            sqlTotal=dsDataStoreTotal.getSqlText();
//            sqlTotal=sqlTotal.replace("$date1","date1");
            sqlTotal=sqlTotal.replace("'$date1'","'"+date1+"'");
//            sqlTotal=sqlTotal.replace("'$date2'","'"+date2+"'");
//            sqlTotal=sqlTotal.replace("'$date3'","'"+date3+"'");
        }
        System.out.println("==========================客户实销上报(按日)sql查询语句："+sql);
        dataList = this.jdbcTemplate.queryForList(sql);
        queryJson.setRows(dataList);
        System.out.println("==========================客户实销上报(按日)【统计】查询语句："+sqlTotal);
        List footRows=this.jdbcTemplate.queryForList(sqlTotal);
//        footRows=null;
        queryJson.setFooter(footRows);
        return queryJson;
    }





    @Override
    public List getfindbxData(String bxId) throws Exception {
        List list = new ArrayList();
        try {
            String sql = "SELECT * FROM CW_BXYXYS b WHERE b.BXDJ_ID = '" + bxId + "'";
            list = this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

}
